package com.zhl.dao;

import com.zhl.dao.dasedao.BaseDao;
import com.zhl.model.User;
import com.zhl.utils.JdbcUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class UserDao extends BaseDao<User> {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtils.getDataSource());

    public UserDao() {
        super(User.class);
    }

    public User FindUserByName(String username){
        User user = null;
        String sql = "select * from user where username = ?";
        try {
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class),username);
        }catch (Exception e){
            return null;
        }
        return user;
    }

    public List<Map<String, Object>> FindAllUser(String realName,Integer id,Integer startIndex,Integer pageSize){
        List<Map<String, Object>> maps = null;
        try {
            if (realName == null || realName == ""){
                String sql = "select u.*,f.user_id from user u " +
                        "left join (select user_id,user_focus_id from userfocus where user_id = ?) f on u.id = f.user_focus_id " +
                        "limit ?,?";
                maps = jdbcTemplate.queryForList(sql, id,startIndex,pageSize);
            }else {
                String sql = "select u.*,f.user_id from user u " +
                        "left join (select user_id,user_focus_id from userfocus where user_id = ?) f on u.id = f.user_focus_id " +
                        "where u.real_name like '%' ? '%' limit ?,?";
                maps = jdbcTemplate.queryForList(sql,id,realName,startIndex,pageSize);
            }
        }catch (Exception e){
            return null;
        }
        return maps;
    }

    public Integer findAll() {
        String sql = "select count(*) as total from user";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql);
        return Integer.parseInt(map.get("total").toString());
    }

    public List<Map<String,Object>> findFocusById(Integer id){
        List<Map<String, Object>> maps = null;
        String sql = "select u.*,f.user_id from user u " +
                "left join (select user_id,user_focus_id from userfocus where user_id = ?) f on u.id = f.user_focus_id";
        try {
            maps =  jdbcTemplate.queryForList(sql,id);
        }catch (Exception e){
            return null;
        }
        return maps;
    }

    public Map<String, Object> findUserAndDeptById(Integer id){
        Map<String, Object> map = null;
        String sql = "select u.id,u.username,u.gender,u.real_name,u.pic,d.name as \"dept_name\" " +
                "from user u " +
                "left join dept d on u.dept_id = d.id " +
                "where u.id = ?";
        try {
            map = jdbcTemplate.queryForMap(sql, id);
        }catch (Exception e){
            return null;
        }
        return map;
    }

    public Map<String, Object> findUserInfoById(Integer id){
        Map<String, Object> map = null;
        String sql = "select u.*,d.name as \"deptName\"," +
                "(select count(user_focus_id) from userfocus where user_id = ?) as \"focus_count\" " +
                "from user u " +
                "left join dept d on u.dept_id = d.id " +
                "where u.id = ?";
        try {
            map = jdbcTemplate.queryForMap(sql,id,id);
        }catch (Exception e){
            return null;
        }
        return map;
    }

    public boolean UpdateById(Integer id){
        int set = 0;
        String sql = "update user set look = look+1 where id = ?";
        try {
            set = jdbcTemplate.update(sql, id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return set > 0;
    }

    public List<Integer> findCollectById(Integer id){
        List<Integer> userCollect = null;
        String sql = "select a_id from favorite  where u_id = ?";
        try {
            userCollect = jdbcTemplate.queryForList(sql,Integer.class,id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return userCollect;
    }

    public List<User> findFocusFavoriteById(Integer uId,Integer aId){
        List<User> focusAndArtList = null;
        String sql = "select * from user " +
                "where id in (select user_focus_id from userfocus " +
                "where user_id = ? and user_focus_id in(select u_id from favorite where a_id = ?))";
        try {
            focusAndArtList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), uId, aId);
        }catch (Exception e){
            return null;
        }
        return focusAndArtList;
    }

    public List<User> findUserByDeptId(Integer deptId){
        List<User> list = null;
        String sql = "select * from user where dept_id = ?";
        try {
            list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), deptId);
        }catch (Exception e){
            return null;
        }
        return list;
    }
}
